import pymysql

db = pymysql.connect(host='192.168.3.110', user='root', password='123456', port=3306)
cursor = db.cursor()
cursor.execute('CREATE DATABASE IF NOT EXISTS student_sql Character Set UTF8MB4')
cursor.close()

db = pymysql.connect(host='192.168.3.110', user='root', password='123456', port=3306, db='student_sql')
cursor = db.cursor()
sql = "CREATE TABLE IF NOT EXISTS students (id CHAR(20), name CHAR(20), age INT)"
cursor.execute(sql)

student = (
    ('0001', 'bob', 12),
    ('0002', 'lucy', 10),
    ('0003', 'kate', 11),
)

try:
    sql = 'INSERT INTO students (id,name,age) VALUES (%s, %s, %s) '
    cursor.executemany(sql, student)
    db.commit()
    sql = 'SELECT * FROM students'
    cursor.execute(sql)
    results = cursor.fetchall()
    print('插入数据后的所有记录：', results)
    sql = 'UPDATE students SET age = %s WHERE name = %s'
    cursor.execute(sql, (13, 'bob'))
    db.commit()
    sql = 'SELECT * FROM students'
    cursor.execute(sql)
    results = cursor.fetchall()
    print('更新数据后记录：', results)
    sql = 'DELETE FROM students WHERE age <= 10'
    cursor.execute(sql)
    db.commit()

    sql = 'SELECT * FROM students'
    cursor.execute(sql)
    results = cursor.fetchall()
    print('删除age小于10的数据后所有记录：', results)



except:
    print('执行失败')
    db.rollback()

cursor.close()
db.close()
